package flst.ipiweb.persistance.dao.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import flst.ipiweb.persistance.dao.ICodePromoDao;
import flst.ipiweb.service.ServiceDate;
import flst.ipiweb.transverse.Article;
import flst.ipiweb.transverse.CodePromotionnel;
import flst.ipiweb.transverse.Enseigne;
import flst.ipiweb.transverse.IPIException;

public class CodePromoDaoJdbc implements ICodePromoDao{
	
	private JdbcUtils jdbcUtils = JdbcUtils.getInstance();
	
	
	@Override
	/**
	 * Cette méthode permet de créer un code promo en base
	 * @param codePromo : le code promo à créer
	 * @return CodePromotionnel : le code promo mis à jour
	 */
	public void createCodePromo(CodePromotionnel codePromo) throws IPIException{
		try
		{
			Connection vConnection = jdbcUtils.getConnection();
			String vRequeteInsert = "INSERT INTO IPII_PROMOTIONAL_CODES (PROMOTIONAL_CODE_ID, PROMOTIONAL_CODE_NUMBER, PROMOTIONAL_CODE_CODE, STORE_ID, PROMOTIONAL_CODE_TYPE, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE,PRODUCT_ID,CREATION_DATE)"
					+"VALUES(SEQ_PROMOTIONAL_CODE.NEXTVAL,?,?,?,?,?,?,?,SYSDATE)";
			
			PreparedStatement vPs =	vConnection.prepareStatement(vRequeteInsert) ;
			//PROMOTIONAL_CODE_NUMBER
			vPs.setInt(1, codePromo.getPourcentage());
			//PROMOTIONAL_CODE_CODE
			vPs.setString(2,codePromo.getIdentifiantCode());
			//STORE_ID
			vPs.setInt(3, codePromo.getEnseigne().getIdEnseigne());
			//PROMOTIONAL_CODE_TYPE_ID
			vPs.setString(4, codePromo.getType());
			//CREATION_DATE
			ServiceDate sDate = new ServiceDate();
			vPs.setDate(5, sDate.convertDateToSqlDate(codePromo.getDateDebut()));
			//EFFECTIVE_END_DATE 
			vPs.setDate(6, sDate.convertDateToSqlDate(codePromo.getDateFin()));
			vPs.setInt(7, codePromo.getArticle().getIdArticle());
			
			vPs.executeUpdate();
			
			vPs.close();
			vConnection.close();
		}
		catch (Exception e) {
			throw new IPIException("CodePromoException.create.technique");
		}
	}
	
	@Override
	/**
	 * Cette méthode permet de récupérer la liste des codes promo en base
	 * @return List<CodePromotionnel> : la liste des codes promo
	 */
	public List<CodePromotionnel> listerCodePromo() throws IPIException{

		List<CodePromotionnel> listeCodePromo = new ArrayList<CodePromotionnel>();
		
		try
		{
			Connection vConnection = jdbcUtils.getConnection();
			Statement vStmt = vConnection.createStatement();
			
			String vRequeteSelect = "SELECT * FROM IPII_PROMOTIONAL_CODES code, IPII_STORES ens WHERE code.STORE_ID = ens.STORE_ID";
			vStmt.execute(vRequeteSelect);
			ResultSet vRs = vStmt.getResultSet();
			while(vRs.next()){
				CodePromotionnel codePromo = new CodePromotionnel();
				//PROMOTIONAL_CODE_ID
				codePromo.setIdCode(vRs.getInt("PROMOTIONAL_CODE_ID"));
				//PROMOTIONAL_CODE_NUMBER
				codePromo.setPourcentage(vRs.getInt("PROMOTIONAL_CODE_NUMBER"));
				//PROMOTIONAL_CODE_CODE
				codePromo.setIdentifiantCode(vRs.getString("PROMOTIONAL_CODE_CODE"));
				//STORE_ID
				Enseigne enseigne = new Enseigne();
				enseigne.setIdEnseigne(vRs.getInt("STORE_ID"));
				enseigne.setLibelle(vRs.getString("STORE_CODE"));
				codePromo.setEnseigne(enseigne);
				//PROMOTIONAL_CODE_TYPE_CODE
				codePromo.setType(vRs.getString("PROMOTIONAL_CODE_TYPE"));
				//EFFECTIVE_START_DATE
				codePromo.setDateDebut(vRs.getDate("EFFECTIVE_START_DATE"));
				//EFFECTIVE_END_DATE
				codePromo.setDateFin(vRs.getDate("EFFECTIVE_END_DATE"));

				listeCodePromo.add(codePromo);
			}
			
			vStmt.close();
			vConnection.close();
		}
		catch(Exception e)
		{
			throw new IPIException("Exception.dao.technique");
		}
		return listeCodePromo;
	}
	
	@Override
	/**
	 * Cette méthode permet de mettre à jour un code promo en base
	 * @param codePromo : le code promo à mettre à jour
	 * @return CodePromotionnel : le code promo mis à jour
	 */
	public void updateCodePromo(CodePromotionnel codePromo) throws IPIException{
		
		try
		{
			Connection vConnection = jdbcUtils.getConnection();
			String vRequeteInsert = "UPDATE IPII_PROMOTIONAL_CODES SET PROMOTIONAL_CODE_NUMBER = ?, PROMOTIONAL_CODE_CODE = ?, PRODUCT_ID = ?,STORE_ID = ?, PROMOTIONAL_CODE_TYPE = ? , EFFECTIVE_START_DATE = ?,EFFECTIVE_END_DATE = ?,LAST_UPDATE_DATE = SYSDATE WHERE PROMOTIONAL_CODE_ID = ?";
			PreparedStatement vPs =	vConnection.prepareStatement(vRequeteInsert) ;
			//PROMOTIONAL_CODE_NUMBER
			vPs.setInt(1, codePromo.getPourcentage());
			//PROMOTIONAL_CODE_CODE
			vPs.setString(2, codePromo.getIdentifiantCode());
			//PRODUCT_ID 
			vPs.setInt(3, codePromo.getArticle().getIdArticle());
			//STORE_ID
			vPs.setInt(4,codePromo.getEnseigne().getIdEnseigne());
			//PROMOTIONAL_CODE_TYPE_ID
			vPs.setString(5, codePromo.getType());
			//EFFECTIVE_START_DATE
			ServiceDate sDate = new ServiceDate();
			vPs.setDate(6, sDate.convertDateToSqlDate(codePromo.getDateDebut()));
			//EFFECTIVE_END_DATE
			vPs.setDate(7, sDate.convertDateToSqlDate(codePromo.getDateFin()));
						
			//code_id
			vPs.setInt(8, codePromo.getIdCode());

			vPs.executeUpdate();
			
			vPs.close();
			vConnection.close();
		}
		catch (Exception e) {
			throw new IPIException("CodePromoException.update.technique");
		}
	}
	
	@Override
	/**
	 * Cette méthode permet de supprimer un code promo en base
	 * @param codePromo : le code promo à supprimer 
	 */
	public void deleteCodePromo(CodePromotionnel codePromo) throws IPIException{
		
		try{
			Connection vConnection = jdbcUtils.getConnection();
			String vRequeteDelete = "DELETE FROM IPII_PROMOTIONAL_CODES WHERE PROMOTIONAL_CODE_ID = ? ";
			PreparedStatement vPs =	vConnection.prepareStatement(vRequeteDelete);
			//code_id
			vPs.setInt(1,codePromo.getIdCode());
			vPs.executeUpdate();
			
			vPs.close();
			vConnection.close();
		}
		catch (Exception e) {
			throw new IPIException("CodePromoException.delete.technique");
		}
		
	}
	
	@Override
	/**
	 * Cette méthode permet de récupérer un code promo en base par son id
	 * @param idCodePromo
	 * @return Code Promotionnel
	 */
	public CodePromotionnel getCodePromoById(int idCodePromo) {
		CodePromotionnel codePromo = new CodePromotionnel();
		
		try
		{
			Connection vConnection = jdbcUtils.getConnection();
			Statement vStmt = vConnection.createStatement();
			
			String vRequeteSelect = "SELECT * FROM IPII_PROMOTIONAL_CODES code, IPII_STORES ens WHERE code.STORE_ID = ens.STORE_ID AND PROMOTIONAL_CODE_ID = ?";
			PreparedStatement vPs = vConnection.prepareStatement(vRequeteSelect);
			// par id
			vPs.setInt(1, idCodePromo);
			ResultSet vRs = vPs.executeQuery();
			vRs.next();
				
			//PROMOTIONAL_CODE_ID
			codePromo.setIdCode(vRs.getInt("PROMOTIONAL_CODE_ID"));
			//PROMOTIONAL_CODE_NUMBER
			codePromo.setPourcentage(vRs.getInt("PROMOTIONAL_CODE_NUMBER"));
			//PROMOTIONAL_CODE_CODE
			codePromo.setIdentifiantCode(vRs.getString("PROMOTIONAL_CODE_CODE"));
			//STORE_ID
			Enseigne enseigne = new Enseigne();
			enseigne.setIdEnseigne(vRs.getInt("STORE_ID"));
			enseigne.setLibelle(vRs.getString("STORE_CODE"));
			codePromo.setEnseigne(enseigne);
			//PROMOTIONAL_CODE_TYPE_CODE
			codePromo.setType(vRs.getString("PROMOTIONAL_CODE_TYPE"));
			//EFFECTIVE_START_DATE
			ServiceDate sDate = new ServiceDate();
			codePromo.setDateDebut(sDate.convertSqlDateToDate(vRs.getDate("EFFECTIVE_START_DATE")));
			//EFFECTIVE_END_DATE
			codePromo.setDateFin(sDate.convertSqlDateToDate(vRs.getDate("EFFECTIVE_END_DATE")));
			//Article
			Article article = new Article();
			article.setIdArticle(vRs.getInt("PRODUCT_ID"));
			codePromo.setArticle(article);

			
			vStmt.close();
			vConnection.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return codePromo;
	}

}
